package com.lyouu.dataminer.util;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.lyouu.dataminer.db.DbConstants;
import com.lyouu.dataminer.db.DbLyouuAttMap;
import com.lyouu.dataminer.factory.MysqlDbFactory;
import com.lyouu.dataminer.model.TourCity;
import com.lyouu.dataminer.model.TourCountry;

public class CategorizerUtil {
	private static Logger logger = Logger.getLogger(CategorizerUtil.class);
	private static ArrayList<DbLyouuAttMap> areaAttList;
	private static String defaultAreaAttId;
	private static ArrayList<DbLyouuAttMap> countryAttList;
	private static String defaultCountryAttId;

	public static ArrayList<DbLyouuAttMap> getAreaAttList() throws SQLException {
		if (areaAttList == null) {
			areaAttList = new ArrayList<DbLyouuAttMap>();
			synchronized (CategorizerUtil.class) {
				String sql = "SELECT attid,type,catid,name FROM md_att_list WHERE type='att' AND catid='1'";
				Statement stmt = MysqlDbFactory.getConnection(
						DbConstants.DB_LYOUU).createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				while (rs.next()) {
					if (rs.getString(4).equals("东欧线路"))
						areaAttList.add(new DbLyouuAttMap(rs.getInt(1), rs
								.getString(2), rs.getInt(3), "东欧"));
					else if (rs.getString(4).equals("南欧线路"))
						areaAttList.add(new DbLyouuAttMap(rs.getInt(1), rs
								.getString(2), rs.getInt(3), "南欧"));
					else if (rs.getString(4).equals("西欧线路"))
						areaAttList.add(new DbLyouuAttMap(rs.getInt(1), rs
								.getString(2), rs.getInt(3), "西欧"));
					else if (rs.getString(4).equals("北欧线路"))
						areaAttList.add(new DbLyouuAttMap(rs.getInt(1), rs
								.getString(2), rs.getInt(3), "北欧"));
					else if (rs.getString(4).equals("其它线路")) {
						defaultAreaAttId = String.valueOf(rs.getInt(1));
						areaAttList.add(new DbLyouuAttMap(rs.getInt(1), rs
								.getString(2), rs.getInt(3), rs.getString(4)));
					} else
						areaAttList.add(new DbLyouuAttMap(rs.getInt(1), rs
								.getString(2), rs.getInt(3), rs.getString(4)));

				}
				// for (int i = 0; i < areaAttList.size(); i++)
				// logger.info(areaAttList.get(i).getName() + "  catid:"
				// + areaAttList.get(i).getCatId() + "  attid:"
				// + areaAttList.get(i).getAttId());
			}
		}
		return areaAttList;
	}

	public static String getDefaultAreaAttId() throws SQLException {
		if (areaAttList == null) {
			synchronized (CategorizerUtil.class) {
				areaAttList = new ArrayList<DbLyouuAttMap>();
				String sql = "SELECT attid,type,catid,name FROM md_att_list WHERE type='att' AND catid='1'";
				Statement stmt = MysqlDbFactory.getConnection(
						DbConstants.DB_LYOUU).createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				while (rs.next()) {
					if (rs.getString(4).equals("东欧线路"))
						areaAttList.add(new DbLyouuAttMap(rs.getInt(1), rs
								.getString(2), rs.getInt(3), "东欧"));
					else if (rs.getString(4).equals("南欧线路"))
						areaAttList.add(new DbLyouuAttMap(rs.getInt(1), rs
								.getString(2), rs.getInt(3), "南欧"));
					else if (rs.getString(4).equals("西欧线路"))
						areaAttList.add(new DbLyouuAttMap(rs.getInt(1), rs
								.getString(2), rs.getInt(3), "西欧"));
					else if (rs.getString(4).equals("北欧线路"))
						areaAttList.add(new DbLyouuAttMap(rs.getInt(1), rs
								.getString(2), rs.getInt(3), "北欧"));
					else if (rs.getString(4).equals("其它线路")) {
						defaultAreaAttId = String.valueOf(rs.getInt(1));
						areaAttList.add(new DbLyouuAttMap(rs.getInt(1), rs
								.getString(2), rs.getInt(3), rs.getString(4)));
					} else
						areaAttList.add(new DbLyouuAttMap(rs.getInt(1), rs
								.getString(2), rs.getInt(3), rs.getString(4)));
				}
			}
		}
		return defaultAreaAttId;
	}

	public static ArrayList<DbLyouuAttMap> getCountryAttList()
			throws SQLException {
		if (countryAttList == null) {
			countryAttList = new ArrayList<DbLyouuAttMap>();
			synchronized (CategorizerUtil.class) {
				String sql = "SELECT attid,type,catid,name FROM md_att_list WHERE type='att' AND catid='2'";
				Statement stmt = MysqlDbFactory.getConnection(
						DbConstants.DB_LYOUU).createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				while (rs.next()) {
					countryAttList.add(new DbLyouuAttMap(rs.getInt(1), rs
							.getString(2), rs.getInt(3), rs.getString(4)));

				}
			}
			// for (int i = 0; i < countryAttList.size(); i++)
			// logger.info(countryAttList.get(i).getName() + "  catid:"
			// + countryAttList.get(i).getCatId() + "  attid:"
			// + countryAttList.get(i).getAttId());

		}
		return countryAttList;
	}

	public static String selectTourArea(String area, String name)
			throws SQLException {
		String tourArea = "";
		name = name.replaceAll("\\(.*\\)", "");
		// logger.info("area: " + area + "    name: " + name);
		area = replaceCityWithCountry(area);
		name = replaceCityWithCountry(name);
		// logger.info("area: " + area + "    name: " + name);
		if (!area.isEmpty()) {
			for (int i = 0; i < CategorizerUtil.getAreaAttList().size(); i++) {
				if (area.contains(CategorizerUtil.getAreaAttList().get(i)
						.getName())
						|| name.contains(CategorizerUtil.getAreaAttList()
								.get(i).getName())) {
					tourArea += CategorizerUtil.getAreaAttList().get(i)
							.getAttId()
							+ ",";
				}
			}
		} else {
			for (int i = 0; i < CategorizerUtil.getAreaAttList().size(); i++) {
				if (name.contains(CategorizerUtil.getAreaAttList().get(i)
						.getName())) {
					tourArea += CategorizerUtil.getAreaAttList().get(i)
							.getAttId()
							+ ",";
				}
			}
		}

		area = replaceCountryWithArea(area);
		name = replaceCountryWithArea(name);
		// logger.info("area: " + area + "    name: " + name);
		if (!area.isEmpty()) {
			for (int i = 0; i < CategorizerUtil.getAreaAttList().size(); i++) {
				if (area.contains(CategorizerUtil.getAreaAttList().get(i)
						.getName())
						|| name.contains(CategorizerUtil.getAreaAttList()
								.get(i).getName())) {
					if (!tourArea.contains(String.valueOf(CategorizerUtil
							.getAreaAttList().get(i).getAttId()))) {
						tourArea += CategorizerUtil.getAreaAttList().get(i)
								.getAttId()
								+ ",";
					}
				}
			}
		} else {
			for (int i = 0; i < CategorizerUtil.getAreaAttList().size(); i++) {
				if (name.contains(CategorizerUtil.getAreaAttList().get(i)
						.getName())) {
					if (!tourArea.contains(String.valueOf(CategorizerUtil
							.getAreaAttList().get(i).getAttId()))) {
						tourArea += CategorizerUtil.getAreaAttList().get(i)
								.getAttId()
								+ ",";
					}
				}
			}
		}
		if (tourArea.isEmpty())
			tourArea = CategorizerUtil.getDefaultAreaAttId();
		else
			tourArea = tourArea.substring(0, tourArea.length() - 1);
		return tourArea;
	}

	public static String selectDepartCountry(String name) throws SQLException {
		String departCountry = "64";// Germany
		if (name.contains("(") || name.contains("（")) {
			if (name.contains("(")) {
				if (name.contains(")"))
					name = name.substring(name.indexOf("(") + 1,
							name.indexOf(")"));
				else
					name = name.substring(name.indexOf("(") + 1, name.length());
			}else{
				if (name.contains("）"))
					name = name.substring(name.indexOf("（") + 1,
							name.indexOf("）"));
				else
					name = name.substring(name.indexOf("（") + 1, name.length());
			}
			name = replaceCityWithCountry(name);
			logger.info("replaced name: " + name);
			for (int i = 0; i < CategorizerUtil.getCountryAttList().size(); i++) {
				if (name.contains(CategorizerUtil.getCountryAttList().get(i)
						.getName())) {
					departCountry = String.valueOf(CategorizerUtil
							.getCountryAttList().get(i).getAttId());
					logger.info("depart country att id: " + departCountry);
					break;
				}
			}
		}
		return departCountry;
	}

	public static String replaceCityWithCountry(String str) {
		if (!str.isEmpty()) {
			ArrayList<TourCity> tc = TourMap.getInstance().getTourCityList();
			for (int i = 0; i < tc.size(); i++) {
				if (!tc.get(i).getNameCn().isEmpty()
						&& str.contains(tc.get(i).getNameCn()))
					str = str.replace(tc.get(i).getNameCn(), tc.get(i)
							.getCountry().getNameCn());
				if (!tc.get(i).getNameEn().isEmpty()
						&& str.contains(tc.get(i).getNameEn()))
					str = str.replace(tc.get(i).getNameEn(), tc.get(i)
							.getCountry().getNameCn());
				if (!tc.get(i).getNameOrig().isEmpty()
						&& str.contains(tc.get(i).getNameOrig()))
					str = str.replace(tc.get(i).getNameOrig(), tc.get(i)
							.getCountry().getNameCn());
			}
		}
		return str;
	}

	public static String replaceCountryWithArea(String str) {
		if (!str.isEmpty()) {
			ArrayList<TourCountry> tc = TourMap.getInstance().getTourCountryList();
			for (int i = 0; i < tc.size(); i++) {
				if (!tc.get(i).getNameCn().isEmpty()
						&& str.contains(tc.get(i).getNameCn()))
					str = str.replace(tc.get(i).getNameCn(), tc.get(i)
							.getArea().getNameCn());
				if (!tc.get(i).getNameEn().isEmpty()
						&& str.contains(tc.get(i).getNameEn()))
					str = str.replace(tc.get(i).getNameEn(), tc.get(i)
							.getArea().getNameCn());
				if (!tc.get(i).getNameOrig().isEmpty()
						&& str.contains(tc.get(i).getNameOrig()))
					str = str.replace(tc.get(i).getNameOrig(), tc.get(i)
							.getArea().getNameCn());
			}
		}
		return str;
	}
}
